﻿using System.Threading.Tasks;

namespace Sidvall.Data
{
    public class SqlProvider : ISqlProvider
    {
        #region Public Members

        #region NameFormat

        public virtual string NameFormat
        {
            get
            {
                return "[{0}]";
            }
        }

        #endregion
        #region WildCard

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1702:CompoundWordsShouldBeCasedCorrectly", MessageId = "WildCard")]
        public virtual string WildCard
        {
            get
            {
                return "%";
            }
        }

        #endregion

        #region CreateDatabaseAsync

        public virtual Task<bool> CreateDatabaseAsync(string connectionString, string database, SqlExceptionBehavior exceptionBehavior)
        {
            return Task.FromResult(false);
        }

        #endregion
        #region ExecuteNonQueryAsync

        public virtual Task<int> ExecuteNonQueryAsync(string connectionString, string sqlQuery, SqlExceptionBehavior exceptionBehavior)
        {
            return Task.FromResult(0);
        }

        #endregion
        #region ExecuteScalarAsync

        public virtual Task<object> ExecuteScalarAsync(string connectionString, string sqlQuery, SqlExceptionBehavior exceptionBehavior)
        {
            return Task.FromResult((object)null);
        }

        #endregion
        #region GetTablesAsync

        public virtual Task<TableItemCollection> GetTablesAsync(string connectionString, bool loadRowCount, SqlExceptionBehavior exceptionBehavior)
        {
            var items = new TableItemCollection();
            return Task.FromResult(items);
        }

        #endregion

        #region GetRelationSyntax

        public virtual string GetRelationSyntax(RelationType relationType)
        {
            switch (relationType)
            {
                case RelationType.Left:
                    return "LEFT JOIN";
                default:
                    return "INNER JOIN";
            }
        }

        #endregion
        #region GetTableName

        public virtual string GetTableName(string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName))
                return null;
            var name = string.Format(System.Globalization.CultureInfo.InvariantCulture, this.NameFormat, tableName);
            return name;
        }

        #endregion
        #region GetFieldName

        public virtual string GetFieldName(string tableName, string fieldName)
        {
            if (string.IsNullOrWhiteSpace(fieldName))
                return null;
            if (fieldName.Contains("."))
            {
                var parts = fieldName.Split('.');
                tableName = parts[0];
                fieldName = parts[1];
            }
            if (string.IsNullOrWhiteSpace(tableName))
                return string.Format(System.Globalization.CultureInfo.InvariantCulture, this.NameFormat, fieldName);
            return string.Format(System.Globalization.CultureInfo.InvariantCulture, this.NameFormat, tableName) + "." +
                string.Format(System.Globalization.CultureInfo.InvariantCulture, this.NameFormat, fieldName);
        }

        #endregion
        #region GetParameterName

        public virtual string GetParameterName(int? index)
        {
            return "?";
        }

        #endregion
        #region GetPropertyValue

        public virtual object GetPropertyValue(object fieldValue, DataType valueDataType)
        {
            return fieldValue;
        }

        #endregion

        #region AddWildCardParameter

        public virtual void AddWildCardParameter(SqlQuery query, ComparisonOperator comparisonOperator, string value)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(value))
                return;
            switch (comparisonOperator)
            {
                case ComparisonOperator.Like:
                case ComparisonOperator.NotLike:
                case ComparisonOperator.In:
                case ComparisonOperator.NotIn:
                    if (!string.IsNullOrWhiteSpace(value))
                        query.ParameterValues.Add(this.WildCard + value + this.WildCard);
                    else
                        query.ParameterValues.Add(this.WildCard);
                    break;
                case ComparisonOperator.StartsWith:
                    query.ParameterValues.Add(value + this.WildCard);
                    break;
                case ComparisonOperator.EndsWith:
                    query.ParameterValues.Add(this.WildCard + value);
                    break;
                default:
                    break;
            }
        }

        #endregion
        #region InitializeSqlComparison

        public virtual void InitializeSqlComparison(SqlQuery query, string expression, string value, ComparisonOperator comparisonOperator, object parameterValue)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(expression))
                return;
            if (string.IsNullOrWhiteSpace(value))
                return;
            var originalValue = value;
            if (parameterValue != null)
                value = GetParameterName(query.NextParameterIndex);
            if (value.Equals("null", System.StringComparison.OrdinalIgnoreCase))
            {
                switch (comparisonOperator)
                {
                    case ComparisonOperator.BitwiseNotEqual:
                    case ComparisonOperator.NotEqual:
                    case ComparisonOperator.NotLike:
                    case ComparisonOperator.NotIn:
                        query.Sql.Append(expression + " IS NOT NULL");
                        break;
                    default:
                        query.Sql.Append(expression + " IS NULL");
                        break;
                }
            }
            else
            {
                switch (comparisonOperator)
                {
                    case ComparisonOperator.BitwiseEqual:
                        query.Sql.Append(expression + " & " + value + " == " + value);
                        break;
                    case ComparisonOperator.BitwiseNotEqual:
                        query.Sql.Append(expression + " & " + value + " == 0");
                        break;
                    case ComparisonOperator.NotEqual:
                        query.Sql.Append(expression + " <> " + value);
                        break;
                    case ComparisonOperator.LessThan:
                        query.Sql.Append(expression + " < " + value);
                        break;
                    case ComparisonOperator.LessThanOrEqual:
                        query.Sql.Append(expression + " <= " + value);
                        break;
                    case ComparisonOperator.GreaterThan:
                        query.Sql.Append(expression + " > " + value);
                        break;
                    case ComparisonOperator.GreaterThanOrEqual:
                        query.Sql.Append(expression + " >= " + value);
                        break;
                    case ComparisonOperator.In:
                    case ComparisonOperator.Like:
                    case ComparisonOperator.EndsWith:
                    case ComparisonOperator.StartsWith:
                        query.Sql.Append(expression + " LIKE " + value);
                        if (parameterValue != null)
                        {
                            AddWildCardParameter(query, comparisonOperator, originalValue);
                            parameterValue = null;
                        }
                        break;
                    case ComparisonOperator.NotLike:
                    case ComparisonOperator.NotIn:
                        query.Sql.Append(expression + " NOT LIKE " + value);
                        if (parameterValue != null)
                        {
                            AddWildCardParameter(query, comparisonOperator, originalValue);
                            parameterValue = null;
                        }
                        break;
                    default:
                        query.Sql.Append(expression + " = " + value);
                        break;
                }
                if (parameterValue != null)
                {
                    query.ParameterValues.Add(parameterValue);
                }
            }
        }

        #endregion
        #region InitializeSqlDelete

        public virtual void InitializeSqlDelete(SqlQuery query, string tableName, SqlQuery whereQuery, RelationItemCollection relations,
            string orderBySql, int? rowStartIndex, int? rowCount)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(tableName))
                return;

            query.Append("DELETE " + GetTableName(tableName) + " ");
            InitializeSqlRelations(query, relations);
            query.Append(whereQuery, " WHERE ");
            query.Append(orderBySql, " ORDER BY ");
        }

        #endregion
        #region InitializeSqlSelect

        public virtual void InitializeSqlSelect(SqlQuery query, string tableName, SqlQuery whereQuery, RelationItemCollection relations,
            string orderBySql, int? rowStartIndex, int? rowCount, string fieldNames)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(tableName))
                return;
            if (string.IsNullOrWhiteSpace(fieldNames))
                return;

            query.Append("SELECT ");
            InitializeSqlPaging(query, rowStartIndex, rowCount);
            query.Append("DISTINCT " + fieldNames + " FROM " + GetTableName(tableName) + " ");
            InitializeSqlRelations(query, relations);
            query.Append(whereQuery, " WHERE ");
            query.Append(orderBySql, " ORDER BY ");
        }

        #endregion
        #region InitializeSqlSelectCount

        public virtual void InitializeSqlSelectCount(SqlQuery query, string tableName, SqlQuery whereQuery, RelationItemCollection relations)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(tableName))
                return;

            query.Append("SELECT COUNT(*) FROM ");
            if ((relations != null) && (relations.Count > 0))
            {
                query.Append("(SELECT DISTINCT " + GetTableName(tableName) + ".* FROM " + GetTableName(tableName) + " ");
                InitializeSqlRelations(query, relations);
                query.Append(whereQuery, " WHERE ");
                query.Append(") AS A");
            }
            else
            {
                query.Append(GetTableName(tableName));
                query.Append(whereQuery, " WHERE ");
            }
        }

        #endregion
        #region InitializeSqlPaging

        public virtual void InitializeSqlPaging(SqlQuery query, int? rowStartIndex, int? rowCount)
        {
            if (query == null)
                return;
            if (rowCount > 0)
            {
                query.Append(" TOP " + rowCount.ToString());
            }
        }

        #endregion
        #region InitializeSqlRelations

        public virtual void InitializeSqlRelations(SqlQuery query, RelationItemCollection relations)
        {
            if (query == null)
                return;
            if (relations == null)
                return;
            foreach (var relation in relations)
            {
                query.Append(GetRelationSyntax(relation.RelationType) + " ");
                if (!string.IsNullOrWhiteSpace(relation.RelatedExpression))
                    query.Append("(" + relation.RelatedExpression + ") ");
                else
                    query.Append(GetTableName(relation.RelatedTableName) + " ");
                if (!string.IsNullOrWhiteSpace(relation.RelatedAlias))
                    query.Append(GetTableName(relation.RelatedAlias) + " ");
                query.Append("ON ");
                var iMax = relation.Fields.Count;
                for (int i = 0; i < iMax; i++)
                {
                    var field = relation.Fields[i];
                    if (i > 0)
                        query.Append(" AND ");
                    query.Append(GetFieldName(relation.SourceAlias, field.SourceFieldName) + "=" + GetFieldName(relation.RelatedAliasResolved, field.RelatedFieldName));
                }
            }
        }

        #endregion

        #endregion
    }
}
